﻿using Future.Common.Models;
using Future.IDAL;
using SqlSugar;
using System.Linq.Expressions;

namespace Future.DAL
{
    /// <summary>
    /// 数据访问基类实现IBaseDAL里面的接口方法
    /// 添加一些额外SQL查询的方法，但又不暴露成接口方法，防止有人在BLL里面写SQL
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class BaseDAL<T> : IBaseDAL<T> where T : class, new()
    {
        /// <summary>
        /// 这里使用属性注入将SqlSugar和普通的DAL解耦
        /// SqlSugar上下文属性 必须是pulic autofac才能属性注入
        /// </summary>
        public ISqlSugarClient SqlSugarClient { get; set; } = default!;

        public bool Delete(T model)
        {
            return SqlSugarClient.Deleteable(model).ExecuteCommand() > 0;
        }

        public bool Delete(Expression<Func<T, bool>> where)
        {
            return SqlSugarClient.Deleteable(where).ExecuteCommand() > 0;
        }

        public async Task<bool> DeleteAsync(Expression<Func<T, bool>> where)
        {
            return await SqlSugarClient.Deleteable(where).ExecuteCommandAsync() > 0;
        }

        public async Task<bool> DeleteAsync(T model)
        {
            return await SqlSugarClient.Deleteable(model).ExecuteCommandAsync() > 0;
        }

        public T Get(Expression<Func<T, bool>> where)
        {
            return SqlSugarClient.Queryable<T>().Where(where).Single();
        }

        public async Task<T> GetAsync(Expression<Func<T, bool>> where)
        {
            return await SqlSugarClient.Queryable<T>().Where(where).SingleAsync();
        }

        public List<T> GetList(Expression<Func<T, bool>> where)
        {
            return SqlSugarClient.Queryable<T>().Where(where).ToList();
        }

        /// <summary>
        /// sql条件查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected List<T> GetList(string sql, Dictionary<string, object> param)
        {
            List<SugarParameter> parameters = BaseDAL<T>.TransformQueryParam(param);
            List<T> result = SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).ToList();
            return result;
        }

        public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> where)
        {
            return await SqlSugarClient.Queryable<T>().Where(where).ToListAsync();
        }

        /// <summary>
        /// sql条件查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        protected async Task<List<T>> GetListAsync(string sql, Dictionary<string, object> param)
        {
            List<SugarParameter> parameters = BaseDAL<T>.TransformQueryParam(param);
            return await SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).ToListAsync();
        }

        public bool Insert(T model)
        {
            return SqlSugarClient.Insertable(model).ExecuteCommand() > 0;
        }

        public async Task<bool> InsertAsync(T model)
        {
            return await SqlSugarClient.Insertable(model).ExecuteCommandAsync() > 0;
        }

        public bool InsertMany(List<T> models)
        {
            return SqlSugarClient.Insertable(models).ExecuteCommand() > 0;
        }

        public async Task<bool> InsertManyAsync(List<T> models)
        {
            return await SqlSugarClient.Insertable(models).ExecuteCommandAsync() > 0;
        }

        public bool Update(T model)
        {
            return SqlSugarClient.Updateable(model).ExecuteCommand() > 0;
        }

        public async Task<bool> UpdateAsync(T model)
        {
            return await SqlSugarClient.Updateable(model).ExecuteCommandAsync() > 0;
        }

        public bool UpdateMany(List<T> models)
        {
            return SqlSugarClient.Updateable(models).ExecuteCommand() > 0;
        }

        public async Task<bool> UpdateManyAsync(List<T> models)
        {
            return await SqlSugarClient.Updateable(models).ExecuteCommandAsync() > 0;
        }

        public PageModel<T> PageList(Expression<Func<T, bool>> where, PageParModel page)
        {
            int total = 0;
            List<T> data = SqlSugarClient.Queryable<T>().Where(where).ToPageList(page.PageNum, page.PageSize, ref total);
            PageModel<T> result = new(data, total);
            return result;
        }

        public async Task<PageModel<T>> PageListAsync(Expression<Func<T, bool>> where, PageParModel page, string? orderBy = null)
        {
            RefAsync<int> total = 0;
            List<T> data;
            if (string.IsNullOrEmpty(orderBy))
            {
                data = await SqlSugarClient.Queryable<T>().Where(where).ToPageListAsync(page.PageNum, page.PageSize, total);
            }
            else
            {
                data = await SqlSugarClient.Queryable<T>().Where(where).OrderBy(orderBy).ToPageListAsync(page.PageNum, page.PageSize, total);
            }
            PageModel<T> result = new(data, total);
            return result;
        }

        /// <summary>
        /// sql分页
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="where">lambda条件</param>
        /// <param name="page">分页参数</param>
        /// <returns></returns>
        protected PageModel<T> PageList(string sql, Expression<Func<T, bool>> where, PageParModel page)
        {
            int total = 0;
            List<T> data = SqlSugarClient.SqlQueryable<T>(sql).Where(where).ToPageList(page.PageNum, page.PageSize, ref total);
            PageModel<T> result = new(data, total);
            return result;
        }

        /// <summary>
        /// sql分页
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="where">lambda条件</param>
        /// <param name="page">分页参数</param>
        /// <returns></returns>
        protected async Task<PageModel<T>> PageListAsync(string sql, Expression<Func<T, bool>> where, PageParModel page)
        {
            RefAsync<int> total = 0;
            List<T> data = await SqlSugarClient.SqlQueryable<T>(sql).Where(where).ToPageListAsync(page.PageNum, page.PageSize, total);
            PageModel<T> result = new(data, total);
            return result;
        }

        /// <summary>
        /// sql分页
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="param">参数化条件 这里使用Dictionary而不是SugarParameter是为了使SqlSugar和普通的DAL解耦</param>
        /// <param name="page">分页参数</param>
        /// <param name="orderBy">排序参数 例：id asc</param>
        /// <returns></returns>
        protected PageModel<T> PageList(string sql, Dictionary<string, object> param, PageParModel page, string? orderBy = null)
        {
            int total = 0;
            List<SugarParameter> parameters = BaseDAL<T>.TransformQueryParam(param);
            List<T> data;
            if (string.IsNullOrEmpty(orderBy))
            {
                data = SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).ToPageList(page.PageNum, page.PageSize, ref total);
            }
            else
            {
                data = SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).OrderBy(orderBy).ToPageList(page.PageNum, page.PageSize, ref total);
            }
            PageModel<T> result = new(data, total);
            return result;
        }

        /// <summary>
        /// sql分页
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="param">参数化条件 这里使用Dictionary而不是SugarParameter是为了使SqlSugar和普通的DAL解耦</param>
        /// <param name="page">分页参数</param>
        /// <param name="orderBy">排序参数 例：id asc</param>
        /// <returns></returns>
        protected async Task<PageModel<T>> PageListAsync(string sql, Dictionary<string, object> param, PageParModel page, string? orderBy = null)
        {
            RefAsync<int> total = 0;
            List<SugarParameter> parameters = BaseDAL<T>.TransformQueryParam(param);
            List<T> data;
            if (string.IsNullOrEmpty(orderBy))
            {
                data = await SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).ToPageListAsync(page.PageNum, page.PageSize, total);
            }
            else
            {
                data = await SqlSugarClient.SqlQueryable<T>(sql).AddParameters(parameters).OrderBy(orderBy).ToPageListAsync(page.PageNum, page.PageSize, total);
            }

            PageModel<T> result = new(data, total);
            return result;
        }

        /// <summary>
        /// 转换查询参数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        private static List<SugarParameter> TransformQueryParam(Dictionary<string, object> param)
        {
            List<SugarParameter> parameters = new();
            if (param != null && param.Any())
            {
                foreach (var item in param)
                {
                    SugarParameter sugarParameter = new($"@{item.Key}", item.Value);
                    parameters.Add(sugarParameter);
                }
            }
            return parameters;
        }
    }
}